const sql = require('mssql');


/**
 * 
 * 拣货异常订单处理系统
 */
/**
 * 
 * 
 * pc端获取异常订单信息
 */
const getPickGoodAbnormalOrderShop = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY M.HWBH) AS px,Y.userid,Y.id,Y.carid,Y.YPBM,Y.SCPH,Z.RQ,Z.DWMC,Z.LXDH,
    Z.JSFSMC,Z.ID_DHD,M.YPMC,M.YPGG,M.JXMC,M.SCCJ,M.SCRQ,M.YXQZ,M.JLDW,M.SL,M.HWBH,B.JP,t3.JHR,t3.YCYY,t3.YCBZ FROM YHDD Y LEFT JOIN XSD_ZB Z 
    ON Y.id = Z.ID LEFT JOIN XSD_MX M ON Y.id = M.ID AND Y.YPBM = M.YPBM AND Y.SCPH = M.SCPH LEFT JOIN BM_YP B ON Y.YPBM = B.BM 
    LEFT JOIN (SELECT * FROM JHCY J LEFT JOIN (SELECT MAX(BZRQ) AS BZ ,ID AS IDS,YPBM AS YPBMS,SCPH AS SCPHS  FROM JHCY  GROUP BY ID,YPBM,SCPH) 
    t2 ON J.ID = t2.IDS AND J.YPBM = t2.YPBMS AND J.SCPH = t2.SCPHS AND J.BZRQ = t2.BZ WHERE t2.BZ IS NOT NULL
    ) t3 ON Y.id = t3.ID AND Y.YPBM = t3.YPBM AND Y.SCPH = t3.SCPH   WHERE Y.SFYC = 'T' `;
    let totalSql = `SELECT  COUNT(Y.id) AS TOTAL FROM YHDD Y LEFT JOIN XSD_ZB Z ON Y.id = Z.ID LEFT JOIN XSD_MX M ON Y.id = M.ID AND Y.YPBM = M.YPBM AND Y.SCPH = M.SCPH LEFT JOIN BM_YP B ON Y.YPBM = B.BM WHERE Y.SFYC = 'T' `;
    if (body.searchValList.RQ[0]) {
        startSql += ` AND Z.RQ >= '${body.searchValList.RQ[0]}' AND Z.RQ <= '${body.searchValList.RQ[1]}' `
        totalSql += ` AND Z.RQ >= '${body.searchValList.RQ[0]}' AND Z.RQ <= '${body.searchValList.RQ[1]}' `
    }
    if (body.searchValList.DWMC) {
        startSql += ` AND (Z.DWMC LIKE '%${body.searchValList.DWMC}%' OR Z.LXDH LIKE '%${body.searchValList.DWMC}%') `
        totalSql += ` AND (Z.DWMC LIKE '%${body.searchValList.DWMC}%' OR Z.LXDH LIKE '%${body.searchValList.DWMC}%') `
    }
    if (body.searchValList.JSFS) {
        startSql += ` AND Z.JSFSBM = '${body.searchValList.JSFS}' `
        totalSql += ` AND Z.JSFSBM = '${body.searchValList.JSFS}' `
    }
    if (body.searchValList.YPBM) {
        startSql += ` AND (Y.YPBM LIKE '%${body.searchValList.YPBM}%' OR M.YPMC LIKE '%${body.searchValList.YPBM}%' OR Y.SCPH LIKE '%${body.searchValList.YPBM}%' OR B.JP LIKE '%${body.searchValList.YPBM}%') `
        totalSql += ` AND (Y.YPBM LIKE '%${body.searchValList.YPBM}%' OR M.YPMC LIKE '%${body.searchValList.YPBM}%' OR Y.SCPH LIKE '%${body.searchValList.YPBM}%' OR B.JP LIKE '%${body.searchValList.YPBM}%') `
    }
    if (body.searchValList.id) {
        startSql += ` AND (Y.id LIKE '%${body.searchValList.id}%' OR Z.ID_DHD LIKE '%${body.searchValList.id}%') `
        totalSql += ` AND (Y.id LIKE '%${body.searchValList.id}%' OR Z.ID_DHD LIKE '%${body.searchValList.id}%') `
    }
    if (body.searchValList.carid) {
        startSql += ` AND Y.carid = '${body.searchValList.carid}'  `
        totalSql += ` AND Y.carid = '${body.searchValList.carid}' `
    }
    startSql += ` )AS t1 WHERE  t1.px between ${(body.pageNum - 1) * 10 + 1} and ${body.pageNum * 10} `
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = { total: total.recordset[0].TOTAL, result: result.recordset }
}

/**
 * 
 * 平板获取异常订单信息
 */
const getMobileAbnormalOrderShop = async (ctx, next) => {
    try{
    const body = ctx.request.body;
    let startSql = `SELECT ROW_NUMBER() OVER(ORDER BY M.HWBH) AS px,Y.userid,Y.id,Y.carid,Y.YPBM,Y.SCPH,Z.RQ,Z.DWMC,Z.LXDH,
    Z.JSFSMC,Z.ID_DHD,M.YPMC,M.YPGG,M.JXMC,M.SCCJ,M.SCRQ,M.YXQZ,M.JLDW,M.SL,M.HWBH,B.JP,t3.JHR,t3.YCYY,t3.YCBZ FROM YHDD Y LEFT JOIN XSD_ZB Z 
    ON Y.id = Z.ID LEFT JOIN XSD_MX M ON Y.id = M.ID AND Y.YPBM = M.YPBM AND Y.SCPH = M.SCPH LEFT JOIN BM_YP B ON Y.YPBM = B.BM 
    LEFT JOIN (SELECT * FROM JHCY J LEFT JOIN (SELECT MAX(BZRQ) AS BZ ,ID AS IDS,YPBM AS YPBMS,SCPH AS SCPHS  FROM JHCY  GROUP BY ID,YPBM,SCPH) 
    t2 ON J.ID = t2.IDS AND J.YPBM = t2.YPBMS AND J.SCPH = t2.SCPHS AND J.BZRQ = t2.BZ WHERE t2.BZ IS NOT NULL
    ) t3 ON Y.id = t3.ID AND Y.YPBM = t3.YPBM AND Y.SCPH = t3.SCPH   WHERE Y.SFYC = 'T' `;

    if (body.userId) {
        startSql += ` AND Y.userid = '${body.userId}' `

    }

    if (body.YPBM) {
        startSql += ` AND (Y.YPBM LIKE '%${body.YPBM}%' OR M.YPMC LIKE '%${body.YPBM}%' OR Y.SCPH LIKE '%${body.YPBM}%' OR B.JP LIKE '%${body.YPBM}%') `

    }

    // if (body.carid) {
    //     startSql += ` AND Y.carid = '${body.searchValList.carid}'  `

    // }

    let result = await sql.query(startSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = {code:200, result: result.recordset }
    } else {
        ctx.body = {code:400, result: [] }
    }
} catch (err) {
    ctx.body = { code: 500, err }
}
    
}

/**
 * 改变异常订单状态
 */
const changePickGoodAbnormalOrderStauts = async (ctx, next) => {
    try {
        const body = ctx.request.body;
        let actionSqlList = [];
        let deleteOldShopSql = `DELETE FROM YHDD WHERE ID = '${body.id}' AND YPBM = '${body.YPBM}'`;
        actionSqlList.push(deleteOldShopSql);
        let newOrderShopSql = `SELECT SCPH FROM XSD_MX WHERE ID = '${body.id}' AND YPBM = '${body.YPBM}'`;
        let newOrderShop = await sql.query(newOrderShopSql);
        let insertSql = `INSERT INTO YHDD (userid,id,carid,YPBM,JHZT,SCPH,FHZT,SFYC) `;
        newOrderShop.recordset.forEach((item, ind) => {
            if (body.choosePeople == 1) {
                if (ind === newOrderShop.recordset.length - 1) {

                    insertSql += ` SELECT '${body.userid}','${body.id}','${body.carid}','${body.YPBM}','F','${item.SCPH}','F','F' `;
                } else {
                    insertSql += ` SELECT '${body.userid}','${body.id}','${body.carid}','${body.YPBM}','F','${item.SCPH}','F','F' UNION `;
                }
            } else if (body.choosePeople == 2) {
                if (ind === newOrderShop.recordset.length - 1) {

                    insertSql += ` SELECT '0722','${body.id}','${body.carid}','${body.YPBM}','F','${item.SCPH}','F','F' `;
                } else {
                    insertSql += ` SELECT '0722','${body.id}','${body.carid}','${body.YPBM}','F','${item.SCPH}','F','F' UNION `;
                }
            } else if (body.choosePeople == 3) {
                if (ind === newOrderShop.recordset.length - 1) {
                    insertSql += ` SELECT '0212','${body.id}','${body.carid}','${body.YPBM}','F','${item.SCPH}','F','F' `;
                } else {
                    insertSql += ` SELECT '0212','${body.id}','${body.carid}','${body.YPBM}','F','${item.SCPH}','F','F' UNION `;
                }
            }
        });
        actionSqlList.push(insertSql);
        let lastSql = ` SET XACT_ABORT ON Begin Tran ${actionSqlList.join(' ')}  Commit Tran `;
        let lastResult = await sql.query(lastSql);
        if (lastResult.rowsAffected[0] > 0) {
            ctx.body = { code: 200, message: '恢复成功' }
        } else {
            ctx.body = { code: 400, message: '恢复失败' }
        }
    } catch (err) {
        ctx.body = { code: 500, err }
    }
}


/**
 * 获取没有货位架的货位架号
 */
const getAbnormalHwbh = async (ctx, next) => {
    const body = ctx.request.body;
    const getHaveHwbh = (hwbhList) => {
        let result = '';
        hwbhList.forEach(item => {
            if (item.HWBH) {
                // return item.HWBH;
                result = item.HWBH;
                // break;
                return false;
            } else {
                result = ''
            }
        })
        return result
    }
    const getResult = () => {
        return new Promise((resolve, reject) => {
            body.shopList.forEach(async (item, ind) => {
                let selectSql = `SELECT HWBH FROM KC_KCDTB WHERE YPBM = '${item.YPBM}' AND KCSL>0 `;
                let result = await sql.query(selectSql);
                let getLastHwbh = getHaveHwbh(result.recordset);
                if (getLastHwbh) {
                    let updateSql = `UPDATE XSD_MX SET HWBH = '${getLastHwbh}' WHERE ID='${item.id}' AND YPBM='${item.YPBM}' `;
                    let updateResult = await sql.query(updateSql);
                    if (updateResult.rowsAffected[0] > 0) {
                        if (ind === body.shopList.length - 1) {
                            resolve(true);
                        }
                    } else {
                        resolve(false);
                    }
                }
            })
        })
    }

    let lastResult = await getResult();
    ctx.body = { success: lastResult }
}

/**
 * 添加备注
 */
const addPickGoodAbnormalRemark = async (ctx, next) => {
    const body = ctx.request.body;
    let updateSql = `UPDATE JHCY SET YCBZ = '${body.YCBZ}' WHERE ID ='${body.id}' AND YPBM ='${body.YPBM}' AND SCPH ='${body.SCPH}'`;
    let result = await sql.query(updateSql);

    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }
}
// 获取结算方式
const getPayStyle = async (ctx, next) => {
    let selectSql = `SELECT BM,MC FROM BM_JSFS WHERE BM > 10`;
    let result = await sql.query(selectSql);
    ctx.body = { result: result.recordset }
}

module.exports = {
    'POST /getPickGoodAbnormalOrderShop': getPickGoodAbnormalOrderShop,
    'POST /getMobileAbnormalOrderShop': getMobileAbnormalOrderShop,

    'POST /getAbnormalHwbh': getAbnormalHwbh,
    'POST /addPickGoodAbnormalRemark': addPickGoodAbnormalRemark,
    'GET /getPayStyle': getPayStyle,
    'POST /changePickGoodAbnormalOrderStauts': changePickGoodAbnormalOrderStauts

}